import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def hello():
    print('dpmtpany operation')


def getAlldpmt():
    print('return all department')

    sql = "SELECT * FROM department"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return 0
    else:
        print("查询部门成功")
        return temp


def newdpmt(dpmt_id, dpmt_name, dpmt_job, job_type, job_desc, attr_com):
    print('insert a new department')

    sql = "insert into department values( '" + dpmt_id + "', '" + dpmt_name + "','" + dpmt_job \
          + "','" + job_type + "','" + job_desc + "','" + attr_com + "') "
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def editdpmt(dpmt_id, dpmt_name, dpmt_job, job_type, job_desc, attr_com):
    print('edit a new department')

    sql = "update department set department_name='" + dpmt_name + "',department_job='" + \
          dpmt_job + "',job_type='" + job_type + "',job_description='" + job_desc + "',attr_com='" + \
          attr_com + "' where department_id='" + dpmt_id + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchdpmt(word):
    sql = "SELECT * FROM department WHERE department_name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的部门")
        return 0
    else:
        print("搜索成功")
        return temp


def deletedpmt(word):
    sql = "Delete FROM department WHERE department_id ='" + str(word) + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchSubdpmt(name):
    sql = "SELECT * FROM department WHERE attr_com = '" + name + "'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的子部门")
        return 0
    else:
        print("搜索成功")
        return temp


def getOptions():
    print('return all subcom')

    # 子公司
    sql = "SELECT distinct subcom_name FROM subcompany"
    print(sql)
    cursor.execute(sql)
    temp1 = cursor.fetchall()

    # 一级部门
    sql = "SELECT distinct department_name FROM department where department_id not in " \
          "(select department_id from department where department_id like '%-%')"
    print(sql)
    cursor.execute(sql)
    temp2 = cursor.fetchall()

    temp = temp1+temp2
    print(temp)
    if temp == ():
        print("没有子公司和一级部门")
        return 0
    else:
        print("查询成功")
        return temp
